package com.laboratory.util.db;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.util.Iterator;

/**
 * @description 读取excel文件内容生成数据库表ddl
 */

/*
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.12</version>
        </dependency>
 */
public class ExcelUtils {

    public static void main(String[] args) {

        String filePath = "D:\\opensource\\util\\src\\main\\java\\com\\laboratory\\util\\db\\表字段-回访.xlsx";
        getDataFromExcel(filePath);

    }

    /**
     * 读取excel文件内容生成数据库表ddl
     *
     * @param filePath excel文件的绝对路径
     */
    public static void getDataFromExcel(String filePath) {
        if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
            System.out.println("文件不是excel类型");
        }
        InputStream fis = null;
        Workbook wookbook = null;
        try {
            fis = new FileInputStream(new File(filePath));
            if (filePath.endsWith(".xls")) {
                try {
                    //2003版本的excel，用.xls结尾
                    wookbook = new HSSFWorkbook(fis);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (filePath.endsWith(".xlsx")) {
                try {
                    //2007版本的excel，用.xlsx结尾
                    wookbook = new XSSFWorkbook(fis);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

            int sheetNum = wookbook.getNumberOfSheets();
            for (int i = 0; i < sheetNum; i++) {
                StringBuilder ddl = new StringBuilder();

                // 是否自增
                boolean autoIncrement = false;
                Sheet sheet = wookbook.getSheetAt(i);
                System.out.println("--------------------------当前读取的sheet页：" + sheet.getSheetName() + "--------------------------");
                // 当前读取行的行号
                int rowId = 1;
                int rownum = sheet.getLastRowNum();
                String tableEnglishName = "";
                String tableChineseName = "";
                for (int j = 0; j < rownum; j++) {
                    Row row = sheet.getRow(j);
                    //获取表英文名
                    Cell cell1 = row.getCell(0);
                    if ((cell1 == null || "".equals(cell1.getStringCellValue())) && ddl.length() > 0) {
                        if (ddl.toString().endsWith(",\r\n")) {
                            ddl = ddl.deleteCharAt(ddl.length() - 3);
                        }
                        ddl.append(") ENGINE=InnoDB " + (autoIncrement ? "AUTO_INCREMENT=1" : "") + " DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci  "
                                + (!"".equals(tableChineseName) ? "COMMENT = '" + tableChineseName + "'" : "") + ";\r\n");
                        tableChineseName = "";
                        ddl.append("\r\n");
                        System.out.println(ddl.toString());
                        writeMessageToFile(ddl.toString());

                        ddl.setLength(0);
                        rowId++;
                        continue;
                    }

                    if (cell1 == null) {
                        continue;
                    }

                    if ("表英文名".equals(cell1.getStringCellValue()) && ddl.length() == 0) {
                        Cell cell2 = row.getCell(1);
                        tableEnglishName = cell2.getStringCellValue();
                        ddl.append("CREATE TABLE " + "`" + tableEnglishName + "` (" + "\r\n");
                        rowId++;
                        continue;
                    }

                    //获取表中文名
                    if ("表中文名".equals(cell1.getStringCellValue())) {
                        Cell cell2 = row.getCell(1);
                        tableChineseName = cell2.getStringCellValue();
                        rowId++;
                        continue;
                    }
                    if ("字段名".equals(cell1.getStringCellValue())) {
                        //校验属性列名称和顺序
                        //字段名	类型	长度,小数点	是否为主键	是否自增	是否非空	注释
                        if (row.getPhysicalNumberOfCells() != 8) {
                            System.out.println("第2行应该只有8个单元格!");
                            return;
                        }
                        Iterator<Cell> cells = row.cellIterator();
                        StringBuilder tableField = new StringBuilder();
                        while (cells.hasNext()) {
                            tableField.append(cells.next().getStringCellValue().trim());
                        }
                        if (!"字段名类型长度,小数点是否为主键是否自增是否非空默认值注释".equals(tableField.toString())) {
                            System.out.println("字段行应该为 字段名\t类型\t长度,小数点\t是否为主键\t是否自增\t是否非空\t默认值\t注释 !");
                            return;
                        }
                        rowId++;
                        continue;
                    }


                    // 字段名
                    String fieldName = row.getCell(0).getStringCellValue();
                    if (fieldName == null | "".equals(fieldName)) {
                        continue;
                    }
                    // 字段类型
                    String fieldType = row.getCell(1).getStringCellValue();
                    // 字段长度
                    Cell cell3 = row.getCell(2);
                    if (cell3 != null) {
                        cell3.setCellType(Cell.CELL_TYPE_STRING);
                    }
                    String fieldLength = cell3 != null ? cell3.getStringCellValue() : null;
                    // 是否为主键
                    Cell cell4 = row.getCell(3);
                    // 是否自增
                    Cell cell5 = row.getCell(4);
                    //是否非空
                    Cell cell6 = row.getCell(5);
                    //默认值
                    Cell cell7 = row.getCell(6);
                    // 字段注释
                    String fieldComment = row.getCell(7) != null ? row.getCell(7).getStringCellValue() : "";


                    ddl.append(
                            "`" + fieldName + "` "
                                    + fieldType
                                    + ((fieldLength != null && !"".equals(fieldLength) && !"0".equals(fieldLength)) ? "(" + fieldLength + ")" : "")
                                    + (("varchar".equals(fieldType) || "text".equals(fieldType)) ? " CHARACTER set utf8mb4 COLLATE utf8mb4_general_ci " : "")
                                    + (cell4 != null && "Y".equals(cell4.getStringCellValue()) ? " PRIMARY KEY " : "")
                                    + (cell5 != null && "Y".equals(cell5.getStringCellValue()) ? " AUTO_INCREMENT " : "")
                                    + (cell6 != null && "N".equals(cell6.getStringCellValue()) ? " NOT NULL" : " DEFAULT NULL")
                                    + (cell7 != null && !"".equals(getValue(cell7)) ? " DEFAULT " + "'" + getValue(cell7) + "'" : "")
                                    + " COMMENT '" + fieldComment + "'"
                                    + (sheet.rowIterator().hasNext() ? ",\r\n" : "\r\n")
                    );
                    if (cell4 != null && "Y".equals(cell5.getStringCellValue())) {
                        autoIncrement = true;
                    }


                    rowId++;
                }

            }


            System.out.println("运行成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                fis.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private static String getValue(Cell cell) {
        if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
            // boolean类型
            return String.valueOf(cell.getBooleanCellValue()).trim();
        } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
            // 数字类型  .数字单元格避免科学计数法
            DecimalFormat df = new DecimalFormat("0");
            return df.format(cell.getNumericCellValue()).trim();
        } else {
            // String类型
            return String.valueOf(cell.getStringCellValue()).trim();
        }
    }

    public static void writeMessageToFile(String message) {
        FileWriter fileWriter = null;
        try {
            if (null == message || "".equals(message)) {
                return;
            }
            System.out.println("开始写入文件");
            File file = new File("D://opensource//util//src//main//java//com//laboratory//util//db//ddl-回访.txt");
            if (!file.exists()) {
                file.createNewFile();
            }
            fileWriter = new FileWriter(file, true);
            fileWriter.write(message);
            System.out.println("写入文件完成");
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fileWriter != null) {
                try {
                    fileWriter.flush();
                    fileWriter.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        }

    }

}